package clothing.database;



	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.ArrayList;

import java.sql.Connection;
	public class SupplierManager {


			dbase db;

			public SupplierManager() {
				db = new dbase();
			}

			public void insert(String name, int contactnum, String street, 
					String city, String state, int zip) {
				try {
					Class.forName("com.mysql.jdbc.Driver").newInstance();
					Connection conn = DriverManager.getConnection(
							"jdbc:mysql://199.17.162.76/Clothing", "root", "");

					System.out.println("Connected");
					//Statement st = conn.createStatement();
					
//					
//					String query = "insert into employees values('','" + firstname
//							+ "','" + mi + "','" + lastname + "','" + phone + "','"
//							+ address + "','" + type + "','" + username + "','"
//							+ password + "')";
//					System.out.println(query);
					
					PreparedStatement p = conn.prepareStatement("insert into suppliers values('',?,?,?,?,?,?)");
					p.setString(1, name);
					p.setInt(2, contactnum);
					p.setString(3, street);
					p.setString(4, city);
					p.setString(5, state);
					p.setInt(6, zip);
					
					p.executeUpdate();
					
				} catch (InstantiationException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

			}

			
			public ArrayList<Supplier> getList() {
				ArrayList<Supplier> list = new ArrayList();

				this.db.open();
				try {
					Statement s = this.db.getConn().createStatement();
					String query = "SELECT * from suppliers";

					ResultSet rs = s.executeQuery(query);

					while (rs.next()) {

						int id = rs.getInt("Supplier ID");
						String Name = rs.getString("Name");
						int Contact_Number = rs.getInt("ContactNumber");
						String Street = rs.getString("Street");
						String City = rs.getString("City");
						String State = rs.getString("State");
						int Zip = rs.getInt("Zip");
						
						Supplier ss = new Supplier(id, Name, Contact_Number, Street, City, State, Zip);
						list.add(ss);
					}

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				return list;
			}
			
		
			
			public void remove(String id)
			{
				this.db.open();
				Statement s;
				try {
					s = this.db.conn.createStatement();

					String query ="Delete from suppliers where `Supplier ID` = '"+id+"'";
					
					s.execute(query);
					
					System.out.println(id + "Deleted Successfully");
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
			public Supplier getSupplier(int id)
			{
				int iid = id;
				String name = "";
				int ContactNumber= 0;
				String street= ""; 
				String city= "";
				String state = "";
				int zip= 0;
				
			
				this.db.open();
				Statement s;
				try {
					s = this.db.conn.createStatement();

					String query ="Select * from suppliers where `Supplier ID` = '"+id+"'";
					
					
					ResultSet rs = s.executeQuery(query);
					while (rs.next()) 
					{
				    iid = rs.getInt("Supplier ID");
				    System.out.println(iid);
					name = rs.getString("Name");
					ContactNumber= rs.getInt("ContactNumber");
					street= rs.getString("Street");
					city = rs.getString("City");
					state= rs.getString("State");
					zip = rs.getInt("Zip");
					}				

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				Supplier sp = new Supplier(iid, name, ContactNumber, street, city, state, zip);
				return sp;
			}
			
			public void updateSupplier(int iid, String Name, int ContactNumber, String Street, String City, String State, int Zip)
		    {
			
		    	this.db.open();
				try {
					Statement s = this.db.getConn().createStatement();
					String query = "UPDATE suppliers SET Name= '"+Name+
							"',`ContactNumber` = '"+ContactNumber+"', Street = '"+Street+"', City = '"+City+"', " +
							"State= '"+State+"', Zip= '" + Zip+"' Where `Supplier ID` = '"+iid +"'";
							
					s.execute(query);
		    }catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		    }
	


}
